﻿using System;
using System.Collections.Generic;
using System.Text;
using TarreOA.TarreOAModels;
using System.Data;
using System.Data.SqlClient;

namespace TarreOA.TarreOADAL
{
    public class FileUploadMappingtbDAO
    {
        //将上传文件的信息保存到数据库中
        public int saveUploadFileInfo(string suln, string iuln, string filePath, DateTime sendDateTime, int emailId)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "insert into FileUploadMappingtb values(@suln, @iuln, @FilePath, @SendDateTime, default, @emailId)";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@suln", suln));
                sqlCmd.Parameters.Add(new SqlParameter("@iuln", iuln));
                sqlCmd.Parameters.Add(new SqlParameter("@FilePath", filePath));
                sqlCmd.Parameters.Add(new SqlParameter("@SendDateTime", sendDateTime));
                sqlCmd.Parameters.Add(new SqlParameter("@emailId", emailId));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        //根据接收人查找自己的文件
        public List<FileUploadMappingtb> findByInceptUserLoginName(string uln)
        {
            List<FileUploadMappingtb> list = null;
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from FileUploadMappingtb where InceptUserLoginName = @uln";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@uln", uln));
                sqlDr = sqlCmd.ExecuteReader();
                UserDAO userDAO = new UserDAO();
                list = new List<FileUploadMappingtb>();
                while (sqlDr.Read())
                {
                    FileUploadMappingtb fileUploadMapping = new FileUploadMappingtb();
                    fileUploadMapping.FileId = (int)sqlDr["FileId"];
                    fileUploadMapping.SendUser = userDAO.FindByLoginName(sqlDr["SendUserLoginName"].ToString());
                    fileUploadMapping.IncpetUser = userDAO.FindByLoginName(sqlDr["InceptUserLoginName"].ToString());
                    fileUploadMapping.FilePath = sqlDr["FilePath"].ToString();
                    fileUploadMapping.SendDateTime = (DateTime)sqlDr["SendDateTime"];
                    fileUploadMapping.InceptState = (int)sqlDr["InceptState"];
                    list.Add(fileUploadMapping);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        //根据上传文件的编号查找文件信息
        public FileUploadMappingtb findByFileId(int fid)
        {
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            FileUploadMappingtb fileUploadMapping = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from FileUploadMappingtb where FileId = @fid";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@fid", fid));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    UserDAO userDAO = new UserDAO();
                    sqlDr.Read();
                    fileUploadMapping = new FileUploadMappingtb();
                    fileUploadMapping.FileId = (int)sqlDr["FileId"];
                    fileUploadMapping.SendUser = userDAO.FindByLoginName(sqlDr["SendUserLoginName"].ToString());
                    fileUploadMapping.IncpetUser = userDAO.FindByLoginName(sqlDr["InceptUserLoginName"].ToString());
                    fileUploadMapping.FilePath = sqlDr["FilePath"].ToString();
                    fileUploadMapping.SendDateTime = (DateTime)sqlDr["SendDateTime"];
                    fileUploadMapping.InceptState = (int)sqlDr["InceptState"];
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return fileUploadMapping;
        }

        //根据发送人查找上传文件信息
        public List<FileUploadMappingtb> findBySendUserLoginName(string suln)
        {
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            List<FileUploadMappingtb> list = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from FileUploadMappingtb where SendUserLoginName = @suln";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@suln", suln));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    UserDAO userDAO = new UserDAO();
                    list = new List<FileUploadMappingtb>();
                    while (sqlDr.Read())
                    {
                        FileUploadMappingtb fileUploadMapping = new FileUploadMappingtb();
                        fileUploadMapping.FileId = (int)sqlDr["FileId"];
                        fileUploadMapping.SendUser = userDAO.FindByLoginName(sqlDr["SendUserLoginName"].ToString());
                        fileUploadMapping.IncpetUser = userDAO.FindByLoginName(sqlDr["InceptUserLoginName"].ToString());
                        fileUploadMapping.FilePath = sqlDr["FilePath"].ToString();
                        fileUploadMapping.SendDateTime = (DateTime)sqlDr["SendDateTime"];
                        fileUploadMapping.InceptState = (int)sqlDr["InceptState"];
                        list.Add(fileUploadMapping);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        //根据接收人和上传时间超找文件信息
        public List<FileUploadMappingtb> findByInceptUserLoginNameAndSendDateTime(string iuln, DateTime sdt)
        {
            List<FileUploadMappingtb> list = null;
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from FileUploadMappingtb where InceptUserLoginName = @iuln and SendDateTime = @sdt";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@iuln", iuln));
                sqlCmd.Parameters.Add(new SqlParameter("@sdt", sdt));
                list = new List<FileUploadMappingtb>();
                sqlDr = sqlCmd.ExecuteReader();
                UserDAO userDAO = new UserDAO();
                while (sqlDr.Read())
                {
                    FileUploadMappingtb fileUploadMapping = new FileUploadMappingtb();
                    fileUploadMapping.FileId = (int)sqlDr["FileId"];
                    fileUploadMapping.SendUser = userDAO.FindByLoginName(sqlDr["SendUserLoginName"].ToString());
                    fileUploadMapping.IncpetUser = userDAO.FindByLoginName(sqlDr["InceptUserLoginName"].ToString());
                    fileUploadMapping.FilePath = sqlDr["FilePath"].ToString();
                    fileUploadMapping.SendDateTime = (DateTime)sqlDr["SendDateTime"];
                    fileUploadMapping.InceptState = (int)sqlDr["InceptState"];
                    list.Add(fileUploadMapping);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        //根据发送人和上传时间超找文件信息
        public List<FileUploadMappingtb> findBySendUserLoginNameAndSendDateTime(string suln, DateTime sdt)
        {
            List<FileUploadMappingtb> list = null;
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from FileUploadMappingtb where SendUserLoginName = @suln and SendDateTime = @sdt";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@suln", suln));
                sqlCmd.Parameters.Add(new SqlParameter("@sdt", sdt));
                list = new List<FileUploadMappingtb>();
                sqlDr = sqlCmd.ExecuteReader();
                UserDAO userDAO = new UserDAO();
                while (sqlDr.Read())
                {
                    FileUploadMappingtb fileUploadMapping = new FileUploadMappingtb();
                    fileUploadMapping.FileId = (int)sqlDr["FileId"];
                    fileUploadMapping.SendUser = userDAO.FindByLoginName(sqlDr["SendUserLoginName"].ToString());
                    fileUploadMapping.IncpetUser = userDAO.FindByLoginName(sqlDr["InceptUserLoginName"].ToString());
                    fileUploadMapping.FilePath = sqlDr["FilePath"].ToString();
                    fileUploadMapping.SendDateTime = (DateTime)sqlDr["SendDateTime"];
                    fileUploadMapping.InceptState = (int)sqlDr["InceptState"];
                    list.Add(fileUploadMapping);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        //根据所属邮件编号查找附件信息
        public List<FileUploadMappingtb> findByEmailId(int eid)
        {
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            List<FileUploadMappingtb> list = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from FileUploadMappingtb where EmailId = @eid";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@eid", eid));
                sqlDr = sqlCmd.ExecuteReader();
                list = new List<FileUploadMappingtb>();
                 UserDAO userDAO = new UserDAO();
                while (sqlDr.Read())
                {
                    FileUploadMappingtb fileUploadMapping = new FileUploadMappingtb();
                    fileUploadMapping.FileId = (int)sqlDr["FileId"];
                    fileUploadMapping.SendUser = userDAO.FindByLoginName(sqlDr["SendUserLoginName"].ToString());
                    fileUploadMapping.IncpetUser = userDAO.FindByLoginName(sqlDr["InceptUserLoginName"].ToString());
                    fileUploadMapping.FilePath = sqlDr["FilePath"].ToString();
                    fileUploadMapping.SendDateTime = (DateTime)sqlDr["SendDateTime"];
                    fileUploadMapping.InceptState = (int)sqlDr["InceptState"];
                    list.Add(fileUploadMapping);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }
    }
}
